-- @owner: @li-xin12345
-- @date: 2023/1/18
-- @testpoint: 设置query_dop=1时，执行含有自定义函数的复杂sql语句时使用函数var_pop

--step1.创建自定义函数;expect:创建成功
create function func_query_dop_0073(text) returns int language sql immutable
as $$ select $1::int;$$;
/

--step2.创建表bmsql_item并插入数据;expect:创建成功，插入数据成功
drop table if exists bmsql_item;
create table bmsql_item (
i_id int not null,
i_name varchar(24),
i_price numeric(5,2),
i_data varchar( 50),
i_im_id int
);
insert into bmsql_item values ('1','sqltest_varchar_1','0.01','sqltest_varchar_1','1') ;
insert into bmsql_item values ('2','sqltest_varchar_2','0.02','sqltest_varchar_2','2') ;
insert into bmsql_item values ('3','sqltest_varchar_3','0.03','sqltest_varchar_3','3') ;
insert into bmsql_item values ('4','sqltest_varchar_4','0.04','sqltest_varchar_4','4') ;
insert into bmsql_item(i_id) values ('5');

--step3.创建表bmsql_history并插入数据;expect:创建成功，插入数据成功
drop table if exists bmsql_history;
create table bmsql_history (
hist_id int,
h_c_id int,
h_c_d_id int,
h_c_w_id int,
h_d_id int,
h_w_id int,
h_date timestamp(6),
h_amount numeric(6,2),
h_data varchar( 24)
);
insert into bmsql_history values('1','1','1','1','1','1',to_timestamp('2010-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'0.01','sqltest_varchar_1') ;
insert into bmsql_history values('2','2','2','2','2','2',to_timestamp('2010-01-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'0.02','sqltest_varchar_2') ;
insert into bmsql_history values('3','3','3','3','3','3',to_timestamp('2010-01-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),'0.03','sqltest_varchar_3') ;
insert into bmsql_history values('4','4','4','4','4','4',to_timestamp('2010-01-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),'0.04','sqltest_varchar_4') ;
insert into bmsql_history(hist_id) values('') ;

--step4.设置query_dop=1时执行含有自定义函数的复杂sql语句时使用函数var_pop;expect:成功
set query_dop = 1;
select func_query_dop_0073(0) c1
union
select distinct var_pop(i_id) c2
from bmsql_item
where i_id not like (with tmp as (select distinct asin(0) as c1 from bmsql_history where bmsql_item.i_im_id <3)
select * from tmp);

--step5.环境清理;expect:成功
drop function func_query_dop_0073;
drop table bmsql_item;
drop table bmsql_history;